package org.MobileSales.DataManagement;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.MobileSales.Model.Params.Param;
import org.MobileSales.Model.Products.*;

import android.app.Service;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Binder;
import android.os.IBinder;
import android.util.Log;

public class ProductsManager {

	public Context context;
	
	public ProductsManager(Context c)
	{
		this.context = c;
	}
	public Product GetProduct(int productId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, TaxId, UnitId, Sku, Name, Description, Price, Quantity, MinQuantity, OrderQuantity, Spread, Symb, MarketingOnly" 
							+ " FROM Products" 
							+ " WHERE _id = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Product result = new Product();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Tax = GetTax(c.getInt(c.getColumnIndex("TaxId")));
     		result.TaxId = c.getInt(c.getColumnIndex("TaxId"));
     		result.Unit = GetUnit(c.getInt(c.getColumnIndex("UnitId")));
     		result.UnitId = c.getInt(c.getColumnIndex("UnitId"));
     		result.Sku = c.getString(c.getColumnIndex("Sku"));
     		result.Description = c.getString(c.getColumnIndex("Description"));
     		result.Price = c.getDouble(c.getColumnIndex("Price"));
     		result.Quantity = c.getDouble(c.getColumnIndex("Quantity"));
     		result.MinQuantity = c.getDouble(c.getColumnIndex("MinQuantity"));
     		result.OrderQuantity = c.getDouble(c.getColumnIndex("OrderQuantity"));
     		result.Spread = c.getDouble(c.getColumnIndex("Spread"));
     		result.Symbol = c.getString(c.getColumnIndex("Symb"));
     		result.IsMarketingOnlyItem = c.getInt(c.getColumnIndex("MarketingOnly")) == 1 ? true : false;    		
     		result.Params = GetProductParams(result.Id);
     		result.Categories = GetProductCategories(result.Id);
     		result.Images = GetProductImages(result.Id);
 		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public Map<Param, String> GetProductParams(int productId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT ParamId, Value"
	    			+ " FROM ProductHasParams" 
	    			+ " WHERE ProductId = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		
     		Map<Param, String> result = new HashMap<Param,String>();
    		if (!c.moveToFirst())
    			return null;
    			ParamsManager pm = new ParamsManager(this.context);
    			
	     	while (c!=null){    	     		
	         	result.put(pm.GetParam(c.getInt(c.getColumnIndex("ParamId"))), c.getString(c.getColumnIndex("Value")));
	         	c.moveToNext();
	     	}
	     	
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;		
	}
	
	public void StoreProductParams(int productId, Map<Param,String> paramMap)
	{
		if (paramMap == null || paramMap.keySet().size() == 0) return;
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
     		String query = "SELECT ParamId"
    			+ " FROM ProductHasParams" 
    			+ " WHERE ProductId = " + productId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> paramsId = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (c!=null){    	     		
	         		paramsId.add(c.getInt(c.getColumnIndex("ParamId")));
	         		c.moveToNext();
	     		}
	     	}
	 		List<Integer> addedParams = new ArrayList<Integer>();
	 		for (Param key : paramMap.keySet()){
	 			addedParams.add(key.Id);
	 			boolean exists = paramsId.contains(key.Id);
	 			
				ContentValues cv = new ContentValues();
				
				cv.put("ProductId", productId);
				cv.put("ParamId",key.Id);
				cv.put("Value", paramMap.get(key));
	
				if (exists)
					db.update("ProductHasParams", cv, "ProductId ="+productId + " and ParamId =" + key.Id, null);
				else
					db.insert("ProductHasParams", null, cv);
	 		}
	 		
	 		for (int paramId : paramsId)
	 		{
	 			if (!addedParams.contains(paramsId))
		 	     	try {
		 				db.delete("ProductHasParams", "ProductId ="+productId + " and ParamId =" + paramId, null);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}		
	}
	
	public void StoreProduct(Product product)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
			
			cv.put("Name",product.Name);
			cv.put("TaxId", product.TaxId);
			cv.put("UnitId", product.UnitId);
			cv.put("Sku", product.Sku);
			cv.put("Description", product.Description);
			cv.put("Price", product.Price);
			cv.put("Quantity", product.Quantity);
			cv.put("MinQuantity", product.MinQuantity);
			cv.put("OrderQuantity", product.OrderQuantity);
			cv.put("Spread", product.Spread);
			cv.put("Symb", product.Symbol);
			cv.put("MarketingOnly", product.IsMarketingOnlyItem);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (product.Id != 0)
				db.update("Products", cv, "_id ="+product.Id, null);
			else
				product.Id = (int)db.insert("Products", null, cv);
			
			StoreProductParams(product.Id, product.Params);
			StoreProductCategories(product.Id, product.Categories);
			for (ProductImage p : product.Images)
				StoreProductImage(p);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void StoreProductCategories(int productId, List<Category> categories)
	{
		if (categories == null || categories.size() == 0) return;
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
     		String query = "SELECT CategoryId"
    			+ " FROM ProductHasCategories" 
    			+ " WHERE ProductId = " + productId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> categoryIds = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (c!=null){    	     		
	     			categoryIds.add(c.getInt(c.getColumnIndex("CategoryId")));
	         		c.moveToNext();
	     		}
	     	}
	 		List<Integer> addedCategories = new ArrayList<Integer>();
	 		for (Category cat : categories){
	 			addedCategories.add(cat.Id);
	 			
	 			if (!categoryIds.contains(cat.Id)){
					ContentValues cv = new ContentValues();
					
					cv.put("ProductId", productId);
					cv.put("CategoryId",cat.Id);
		
					db.insert("ProductHasCategories", null, cv);
	 			}
	 		}
	 		
	 		for (int catId : categoryIds)
	 		{
	 			if (!addedCategories.contains(catId))
		 	     	try {
		 				db.delete("ProductHasCategories", "ProductId ="+productId + " and CategoryId =" + catId, null);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	finally {
     		dbHelper.close();
     	}	
	}
	public Tax GetTax(int taxId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, Value" 
							+ " FROM Taxes" 
							+ " WHERE _id = " + taxId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Tax result = new Tax();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Value = c.getDouble(c.getColumnIndex("Value"));
     		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public void StoreTax(Tax tax)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
				
			cv.put("Value",tax.Value);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (tax.Id != 0)
				db.update("Taxes", cv, "_id ="+tax.Id, null);
			else
				db.insert("Taxes", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveTax(int taxId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("Taxes", "_id ="+taxId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}

	public Unit GetUnit(int unitId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, Name" 
							+ " FROM Units" 
							+ " WHERE _id = " + unitId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Unit result = new Unit();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public void StoreUnit(Unit unit)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
				
			cv.put("Name",unit.Name);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (unit.Id != 0)
				db.update("Taxes", cv, "_id ="+unit.Id, null);
			else
				db.insert("Taxes", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveUnit(int unitId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("Units", "_id ="+unitId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public List<Category> GetProductCategories(int productId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT CategoryId" 
							+ " FROM ProductHasCategories" 
							+ " WHERE ProductId = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		List<Category> result = new LinkedList<Category>();
     		while (c!=null){
	     		int catId = c.getInt(c.getColumnIndex("CategoryId"));
	     		
	     		result.add(GetCategory(catId));
	     		c.moveToNext();
     		}
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
		return null;
	}
	public Category GetCategory(int categoryId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, ParentCategoryId, Name, Description" 
							+ " FROM Categories" 
							+ " WHERE _id = " + categoryId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Category result = new Category();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Description = c.getString(c.getColumnIndex("Description"));
     		result.ParentId = c.getInt(c.getColumnIndex("ParentCategoryId"));
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public void StoreCategory(Category category)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
				
			cv.put("Name",category.Name);
			cv.put("Description", category.Description);
			cv.put("ParentCategoryId", category.ParentId);
			
			SQLiteDatabase db = dbHelper.getDatabase();
			if (category.Id != 0)
				db.update("Categories", cv, "_id ="+category.Id, null);
			else
				db.insert("Categories", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveCategory(int categoryId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("Categories", "_id ="+categoryId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public List<ProductImage> GetProductImages(int productImageId)
	{
    	final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
     		dbHelper.openDatabase();
     		SQLiteDatabase db = dbHelper.getDatabase();
     		
     		String query = "SELECT _id, ProductId, Path, Position" 
							+ " FROM ProductImage" 
							+ " WHERE _id = " + productImageId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		List<ProductImage> result = new LinkedList<ProductImage>();
     		while (c!=null){
	     		ProductImage p = new ProductImage();
	     		p.Id = c.getInt(c.getColumnIndex("_id"));
	     		//result = c.getInt(c.getColumnIndex("ProductId"));
	     		p.Path = c.getString(c.getColumnIndex("Path"));
	     		p.Position = c.getInt(c.getColumnIndex("Position"));
	     		
	     		result.add(p);
	     		c.moveToNext();
     		}
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}finally{
     		dbHelper.close();
     	}
     	
		return null;
	}
	
	public void StoreProductImage(ProductImage productImage)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			ContentValues cv = new ContentValues();
				
			cv.put("Path",productImage.Path);
			cv.put("Position", productImage.Position);

			SQLiteDatabase db = dbHelper.getDatabase();
			if (productImage.Id != 0)
				db.update("ProductImage", cv, "_id ="+productImage.Id, null);
			else
				db.insert("ProductImage", null, cv);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
	
	public void RemoveProductImage(int productImageId)
	{
		final DatabaseHelper dbHelper = new DatabaseHelper(this.context);
     	try {
			SQLiteDatabase db = dbHelper.getDatabase();
			db.delete("ProductImage", "_id ="+productImageId, null);
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}
     	finally {
     		dbHelper.close();
     	}
	}
}
